package com.glsc.ngateway.common.base.repo.oracle.ecology;

import com.glsc.ngateway.common.base.domain.oracle.ecology.OaDemandFlowSum;
import com.glsc.ngateway.common.base.dto.oaecology.OaDemandFlowSumQueryDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;


@Repository
public interface OaDemandFlowSumRepository extends JpaRepository<OaDemandFlowSum, Long>, JpaSpecificationExecutor<OaDemandFlowSum> {

    @Query(value = "SELECT FLOW_TYPE,REQUESTID,SQDH,CREATE_USER_ID_OA,ASSIGN_USER_ID_LIST_OA,COLLABORATOR_ID_LIST_OA,RELATED_SYSTEM_ID_OA,RELATED_SYSTEM_NAME,REQUEST_CONTENT,SOLUTION_CONTENT,SOLUTION_FILE_ID,DEMAND_FILE_ID_LIST,OTHER_REMARK,RELATED_REQUESTID_LIST,REQUESTNAME,CURRENTNODEID,CURRENTNODETYPE,CURRENTSTATUS,CREATEDATE,CREATETIME,FLOW_CREATE_ID_OA,CREATE_USER_NAME,ASSIGN_USER_ID_OA1,ASSIGN_USER_ID_OA2,ASSIGN_USER_ID_OA3,ASSIGN_USER_ID_OA4 " +
            "FROM v_gw_it_demand_flow_sum WHERE 1=1 " +
            "and ( :#{#param.assignUserIdOa} is null       or MY_FIND_IN_SET (:#{#param.assignUserIdOa}      ,ASSIGN_USER_ID_LIST_OA)>0 ) " +
            "and ( :#{#param.collaboratorUserIdOa} is null or MY_FIND_IN_SET (:#{#param.collaboratorUserIdOa},ASSIGN_USER_ID_LIST_OA)>0 ) " +
            "and ( :#{#param.requestName} is null or REQUESTNAME like CONCAT( CONCAT('%', :#{#param.requestName}), '%') ) " +
            "and ( CREATEDATE between :#{#param.requestBeginDate} and :#{#param.requestEndDate} ) "
            , nativeQuery = true)
    Page<OaDemandFlowSum> page(OaDemandFlowSumQueryDto param, Pageable pageable);
}
